package oa.dao.impl;

import java.util.List;

import oa.base.BaseDao;
import oa.dao.AttendanceDao;
import oa.entity.Attendance;
import oa.exception.DaoException;

public class AttendanceDaoImpl extends BaseDao<Attendance> implements AttendanceDao {

	@Override
	public List<Attendance> selectAllAttendance() throws DaoException {
		return executeQuery("SELECT * FROM attendance");
	}

	@Override
	public boolean addAttendance(Attendance attendance) throws DaoException {
		return executeUpdate(
				"INSERT INTO `oa`.`attendance` (`attendance_starttime`,  `user_id`, `dept_id`, `company_id`) VALUES (?,  ?, ?, ?)",
				attendance.getAttendanceStarttime(), attendance.getUserId(), attendance.getDeptId(),
				attendance.getCompanyId()) > 0;
	}

	@Override
	public boolean updateAttendance(Attendance attendance) throws DaoException {
		return executeUpdate("UPDATE `oa`.`attendance` SET `attendance_endtime` = ? WHERE `attendance_id` =?",
				attendance.getAttendanceEndtime(), attendance.getAttendanceId()) > 0;
	}

	@Override
	public boolean delAttendance(int id) throws DaoException {
		return executeUpdate("DELETE FROM attendance WHERE attendance_id = ?", id) > 0;
	}

	@Override
	public List<Attendance> selectTimeUserid(Attendance attendance) throws DaoException {
		if (attendance.getAttendanceStarttime() != null) {
			return executeQuery(
					"SELECT * FROM `attendance`  WHERE DATE_FORMAT(attendance_starttime,'%Y-%m-%d')= DATE_FORMAT(?,'%Y-%m-%d') AND user_id = ?",
					attendance.getAttendanceStarttime(), attendance.getUserId());
		} else if (attendance.getAttendanceEndtime() != null) {
			return executeQuery(
					"SELECT * FROM `attendance`  WHERE DATE_FORMAT(attendance_endtime,'%Y-%m-%d')= DATE_FORMAT(?,'%Y-%m-%d') AND user_id = ?",
					attendance.getAttendanceEndtime(), attendance.getUserId());

		} else {
			return executeQuery("SELECT * FROM `attendance` ");

		}

	}

	@Override
	public int selectCountList(String date, Attendance attendance) throws DaoException {
		if (date != null && attendance != null) {

			return executeIntQuery(
					"SELECT COUNT(`attendance_starttime`) FROM `attendance` WHERE  attendance_starttime !='0000-00-00 00:00:00' AND DATE_FORMAT(attendance_starttime,'%Y-%m')= ? AND  dept_id= ? AND company_id=?",
					date, attendance.getDeptId(), attendance.getCompanyId());
		}
		return 0;

	}

	@Override
	public List<Attendance> selectStartTime(int userId, String startTime, String endTime) throws DaoException {
		if (!startTime.equals(null)) {
			return executeQuery(
					"SELECT * FROM `attendance` WHERE  DATE_FORMAT(attendance_starttime,'%Y-%m')=? AND `user_id` = ? ORDER BY attendance_starttime ASC",
					startTime, userId);
		} else if (!endTime.equals(null)) {
			return executeQuery(
					"SELECT * FROM `attendance` WHERE  DATE_FORMAT(attendance_endtime,'%Y-%m')=? AND `user_id` = ? ORDER BY attendance_endtime ASC",
					endTime, userId);
		}
		return null;
	}

	@Override
	public List<Attendance> selectAttendance(int deptId, int userId, int attendanceuserid) throws DaoException {
		String sql = "SELECT * FROM attendance";
		Object obj = "";
		if (deptId != 0) {
			sql += " WHERE dept_id = ?";
			obj = deptId;
		} else if (userId != 0) {
			sql += " WHERE user_id = ?";
			obj = userId;
		} else if (attendanceuserid != 0) {
			sql = "SELECT * FROM `user`,`attendance` WHERE `user`.user_id = `attendance`.user_id AND `attendance`.user_id =?";
			obj = attendanceuserid;

		}
		return executeQuery(sql, obj);
	}

	@Override
	public List<Attendance> selectFenYe(int attendanceuserid, Integer pageNo, Integer row) {
		try {
			return executeQuery(
					"SELECT * FROM `user`,`attendance` WHERE `user`.user_id = `attendance`.user_id AND `attendance`.user_id =? LIMIT ?,?",
					attendanceuserid, pageNo, row);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return null;
	}

	@Override
	public int SecletCountSum(int attendanceuserid) {
		try {
			return executeIntQuery("SELECT COUNT(*) FROM `attendance` WHERE `attendance`.user_id =?", attendanceuserid);

		} catch (Exception e) {
			// TODO: handle exception
		}
		return attendanceuserid;
	}

	@Override
	public int selectCountLists(String date, int ccompanyid) throws DaoException {
		if (date != null && ccompanyid != 0) {
			return executeIntQuery(
					"SELECT count(*)from attendance WHERE DATE_FORMAT(attendance_starttime,'%Y-%m-%d')=? AND company_id=?",
					date, ccompanyid);
		} else if (date != null&&ccompanyid == 0) {
			return executeIntQuery(
					"SELECT count(*)from attendance WHERE DATE_FORMAT(attendance_starttime,'%Y-%m-%d')=?", date);
		}
		return 0;
	}
}
